package com.telecom.cms.dao;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.telecom.cms.dto.SysDepart;
import com.telecom.cms.dto.SysDepartEx;
import com.telecom.cms.util.Constant;
import com.telecom.cms.util.CurrentTime;

public class SysDepartDAO extends DaoImp {

	Dao dao = new DaoImp();

	String db = (new Constant()).getPara("DATABASE");

	// property constants

	public int insert(SysDepart transientInstance) {
		int rs = 0;
		String sql = "select * from  " + db + ".Sys_Depart where depart_id="
				+ transientInstance.getDepartId();
		try {
			ResultSet rs2 = dao.select(sql);
			if (rs2.next())
				sql = "update " + db + ".Sys_Depart set depart_name='"
						+ transientInstance.getDepartName()
						+"',latn_Id='"
						+transientInstance.getLatnId()
						+ "',par_depart_id="
						+ transientInstance.getParDepartId()
						+ ",depart_desc='" + transientInstance.getDepartDesc()
						+ "',Creator_Name='"
						+ transientInstance.getCreatorName() + "',Create_Time=timestamp'"
						+ CurrentTime.getCurrentTime()
						+ "' where depart_id=" + transientInstance.getDepartId();
			else
				sql = "insert into " + db + ".Sys_Depart values(,'"
						+ transientInstance.getLatnId() + "','"
						+ transientInstance.getDepartName() + "',"
						+ transientInstance.getParDepartId() + ",'"
						+ transientInstance.getDepartDesc() + "','"
						+ transientInstance.getCreatorName()+ "',timestamp'"
						+ CurrentTime.getCurrentTime()  + "')";
			rs = dao.insert(sql);
			
			if (rs2 != null) {

				rs2.close();
			}

		} catch (Exception re) {
			System.out.println("save failed:" + re);
		}finally
		{
			dao.closeconn();
		}
		return rs;
	}

	public void delete(SysDepart persistentInstance) {

		String sql = "delete from " + db + ".Sys_Depart where Depart_Id = "
				+ persistentInstance.getDepartId();

		try {
			dao.delete(sql);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public SysDepart findById(int id) {

		String sql = "select * from " + db + ".Sys_Depart where Depart_Id = "
				+ id;
		SysDepart sd = new SysDepart();
		try {
			ResultSet rs = dao.select(sql);
			if (rs.next()) {

				sd.setDepartId(id);
				sd.setLatnId(rs.getString(2));
				sd.setDepartName(rs.getString(3));
				sd.setParDepartId(rs.getInt(4));
				sd.setDepartDesc(rs.getString(5));
				sd.setCreatorName(rs.getString(6));
				sd.setCreateTime(rs.getString(7));

			}
			
			if (rs != null) {

				rs.close();
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally
		{
			dao.closeconn();
		}
		return sd;

	}

	/*public List findAll() {

		String sql = "select * from " + db + ".Sys_Depart";

		List list = new ArrayList();
		try {
			ResultSet rs = dao.select(sql);
			while (rs.next()) {
				SysDepart sd = new SysDepart();
				sd.setDepartId(rs.getInt(1));
				sd.setLatnId(rs.getString(2));
				sd.setDepartName(rs.getString(3));
				sd.setParDepartId(rs.getInt(4));
				sd.setDepartDesc(rs.getString(5));
				sd.setCreatorName(rs.getString(6));
				sd.setCreateTime(rs.getTimestamp(7));

				list.add(sd);
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}*/
	public List findAll() {

		//String sql = "select * from " + db + ".Sys_Depart";
		
		String sql = "select a.*,b.latn_name,c.depart_name from " + db + ".Sys_Depart a "+
		"left join " + db + ".sys_loc_latn b "+
		"on a.latn_id=b.latn_id "+
		"left join " + db + ".Sys_Depart c "+
		"on a.par_depart_id=c.depart_id";

		List list = new ArrayList();
		SysDepartEx sd =null;
		try {
			ResultSet rs = dao.select(sql);
			while (rs.next()) {
				sd = new SysDepartEx();
				sd.setDepartId(rs.getInt(1));
				sd.setLatnId(rs.getString(2));
				sd.setDepartName(rs.getString(3));
				sd.setParDepartId(rs.getInt(4));
				sd.setDepartDesc(rs.getString(5));
				sd.setCreatorName(rs.getString(6));
				sd.setCreateTime(rs.getString(7));
				sd.setParDepartName(rs.getString(9));
				sd.setLatnName(rs.getString(8));

				list.add(sd);
			}
			if (rs != null) {

				rs.close();
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally
		{
			dao.closeconn();
		}
		return list;
	}
	
	public List findAll(String latnId) {

		//String sql = "select * from " + db + ".Sys_Depart"; 
		
		String sql = "select a.*,b.latn_name,c.depart_name from " + db + ".Sys_Depart a "+
		"left join " + db + ".sys_loc_latn b "+
		"on a.latn_id=b.latn_id "+
		"left join " + db + ".Sys_Depart c "+
		"on a.par_depart_id=c.depart_id " +
		"where a.latn_id='" + latnId + "'";

		List list = new ArrayList();
		SysDepartEx sd =null;
		try {
			ResultSet rs = dao.select(sql);
			while (rs.next()) {
				sd = new SysDepartEx();
				sd.setDepartId(rs.getInt(1));
				sd.setLatnId(rs.getString(2));
				sd.setDepartName(rs.getString(3));
				sd.setParDepartId(rs.getInt(4));
				sd.setDepartDesc(rs.getString(5));
				sd.setCreatorName(rs.getString(6));
				sd.setCreateTime(rs.getString(7));
				sd.setParDepartName(rs.getString(9));
				sd.setLatnName(rs.getString(8));

				list.add(sd);
			}
			if (rs != null) {

				rs.close();
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally
		{
			dao.closeconn();
		}
		return list;
	}

}